package com.termine.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import javax.faces.bean.ApplicationScoped;
import javax.faces.bean.ManagedBean;

import com.termine.bean.Bean;
import com.termine.db.Connector;
import com.termine.entity.Appointment;
import com.termine.entity.ClientProviderScheduleEvent;

@ManagedBean
@ApplicationScoped
public class AppointmentDAO extends Bean {

	public void addAppointment(Appointment appointment) throws Exception {
		Connection connection = null;
		PreparedStatement statement = null;
		String statementString = "insert into APPOINTMENT (L_ID, S_ID, C_ID, AP_START, AP_END) values (?,?,?,?,?)";
		try {
			connection = Connector.getConnection();
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, appointment.getLocation_id());
			statement.setInt(2, appointment.getService_id());
			statement.setInt(3, appointment.getClient_id());
			statement.setTimestamp(4, new Timestamp(appointment.getStart()
					.getTime()));
			statement.setTimestamp(5, new Timestamp(appointment.getEnd()
					.getTime()));
			statement.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
			Connector.closeConnection(connection);
		}
	}
	
	public void deleteAppointment(Integer appointmentId) throws Exception {
		Connection connection = null;
		PreparedStatement statement = null;
		String statementString = "delete from APPOINTMENT where AP_ID = ?";
		try {
			connection = Connector.getConnection();
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, appointmentId);
			statement.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
			Connector.closeConnection(connection);
		}
	}

	public List<ClientProviderScheduleEvent> getAllAppointmentsForClient(
			Integer clientId) throws Exception {
		Connection connection = null;
		PreparedStatement statement = null;
		String statementString = "select * from APPOINTMENT a join LOCATION l on l.L_ID = a.L_ID "
				+ "join SERVICE s on s.S_ID = a.S_ID join PROVIDER p on p.P_ID = s.P_ID "
				+ "join PERSONAL_DATA pd on pd.PD_ID = p.PD_ID where a.C_ID = ?";
		try {
			connection = Connector.getConnection();
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, clientId);
			ResultSet resultSet = statement.executeQuery();
			List<ClientProviderScheduleEvent> result = null;
			if (!resultSet.isClosed()) {
				result = new LinkedList<ClientProviderScheduleEvent>();
				while (resultSet.next()) {
					ClientProviderScheduleEvent appointment = new ClientProviderScheduleEvent();
					Integer integer;
					String string;
					Date date;

					integer = new Integer(resultSet.getInt("L_ID"));
					if (!resultSet.wasNull()) {
						appointment.setLocationId(integer);
					}
					integer = new Integer(resultSet.getInt("AP_ID"));
					if (!resultSet.wasNull()) {
						appointment.setAppointmentId(integer);
					}
					date = new Date(resultSet.getTimestamp("AP_START")
							.getTime());
					if (!resultSet.wasNull()) {
						appointment.setAppointmentStart(date);
					}
					date = new Date(resultSet.getTimestamp("AP_END").getTime());
					if (!resultSet.wasNull()) {
						appointment.setAppointmentEnd(date);
					}
					string = resultSet.getString("S_NAME");
					if (!resultSet.wasNull()) {
						appointment.setServiceName(string);
					}
					string = resultSet.getString("S_DESCRIPTION");
					if (!resultSet.wasNull()) {
						appointment.setServiceDescription(string);
					}
					integer = new Integer(
							resultSet.getInt("S_CANCEL_DEADLINE_IN_MIN"));
					if (!resultSet.wasNull()) {
						appointment.setServiceCancelDeadlineInMin(integer);
					}
					string = resultSet.getString("PD_FORENAME");
					if (!resultSet.wasNull()) {
						appointment.setClientProviderForename(string);
					}
					string = resultSet.getString("PD_SIRNAME");
					if (!resultSet.wasNull()) {
						appointment.setClientProviderSirname(string);
					}
					string = resultSet.getString("PD_PHONE_NUMBER");
					if (!resultSet.wasNull()) {
						appointment.setClientProviderPhoneNumber(string);
					}
					string = resultSet.getString("PD_EMAIL");
					if (!resultSet.wasNull()) {
						appointment.setClientProviderEmail(string);
					}
					string = resultSet.getString("L_COUNTRY");
					if (!resultSet.wasNull()) {
						appointment.setLocationCountry(string);
					}
					string = resultSet.getString("L_CITY");
					if (!resultSet.wasNull()) {
						appointment.setLocationCity(string);
					}
					string = resultSet.getString("L_ZIP");
					if (!resultSet.wasNull()) {
						appointment.setLocationZip(string);
					}
					string = resultSet.getString("L_STREET");
					if (!resultSet.wasNull()) {
						appointment.setLocationStreet(string);
					}
					result.add(appointment);
				}
			}
			return result;
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
			Connector.closeConnection(connection);
		}
	}
	
	public List<ClientProviderScheduleEvent> getAllAppointmentsForProvider(
			Integer providerId) throws Exception {
		Connection connection = null;
		PreparedStatement statement = null;
		String statementString = "select * from SERVICE s join APPOINTMENT a on s.S_ID = a.S_ID " +
                        "join CLIENT c on c.C_ID = a.C_ID join PERSONAL_DATA pd on c.C_ID = pd.C_ID " +
                        "join LOCATION l on a.L_ID = l.L_ID where s.P_ID = ?";
		try {
			connection = Connector.getConnection();
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, providerId);
			ResultSet resultSet = statement.executeQuery();
			List<ClientProviderScheduleEvent> result = null;
			if (!resultSet.isClosed()) {
				result = new LinkedList<ClientProviderScheduleEvent>();
				while (resultSet.next()) {
					ClientProviderScheduleEvent appointment = new ClientProviderScheduleEvent();
					Integer integer;
					String string;
					Date date;

					integer = new Integer(resultSet.getInt("L_ID"));
					if (!resultSet.wasNull()) {
						appointment.setLocationId(integer);
					}
					integer = new Integer(resultSet.getInt("AP_ID"));
					if (!resultSet.wasNull()) {
						appointment.setAppointmentId(integer);
					}
					date = new Date(resultSet.getTimestamp("AP_START")
							.getTime());
					if (!resultSet.wasNull()) {
						appointment.setAppointmentStart(date);
					}
					date = new Date(resultSet.getTimestamp("AP_END").getTime());
					if (!resultSet.wasNull()) {
						appointment.setAppointmentEnd(date);
					}
					string = resultSet.getString("S_NAME");
					if (!resultSet.wasNull()) {
						appointment.setServiceName(string);
					}
					string = resultSet.getString("S_DESCRIPTION");
					if (!resultSet.wasNull()) {
						appointment.setServiceDescription(string);
					}
					integer = new Integer(
							resultSet.getInt("S_CANCEL_DEADLINE_IN_MIN"));
					if (!resultSet.wasNull()) {
						appointment.setServiceCancelDeadlineInMin(integer);
					}
					string = resultSet.getString("PD_FORENAME");
					if (!resultSet.wasNull()) {
						appointment.setClientProviderForename(string);
					}
					string = resultSet.getString("PD_SIRNAME");
					if (!resultSet.wasNull()) {
						appointment.setClientProviderSirname(string);
					}
					string = resultSet.getString("PD_PHONE_NUMBER");
					if (!resultSet.wasNull()) {
						appointment.setClientProviderPhoneNumber(string);
					}
					string = resultSet.getString("PD_EMAIL");
					if (!resultSet.wasNull()) {
						appointment.setClientProviderEmail(string);
					}
					string = resultSet.getString("L_COUNTRY");
					if (!resultSet.wasNull()) {
						appointment.setLocationCountry(string);
					}
					string = resultSet.getString("L_CITY");
					if (!resultSet.wasNull()) {
						appointment.setLocationCity(string);
					}
					string = resultSet.getString("L_ZIP");
					if (!resultSet.wasNull()) {
						appointment.setLocationZip(string);
					}
					string = resultSet.getString("L_STREET");
					if (!resultSet.wasNull()) {
						appointment.setLocationStreet(string);
					}
					result.add(appointment);
				}
			}
			return result;
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
			Connector.closeConnection(connection);
		}
	}
}
